# -*- coding: utf-8 -*-
"""
Created on Wed Nov 18 23:08:59 2020
key_split:key切分提取
@author: DELL
"""
from pandas import read_pickle
import pickle
from copy import deepcopy
import sys
sys.path.append("..")
from MYSQL import mysqldb
from tjdutils.utils import current_time, get_file_list

def key_split(key:str):
    key_dict = eval(key)
    y_name, freq, end_datetime, test_len, periods, feature_selector = key_dict["y_name"], key_dict["freq"], key_dict["end_datetime"], key_dict["test_len"], key_dict["periods"], key_dict["selector"]
    score_func, mode, method, estimator, metric = None, None, None, None, None
    if key_dict['selector'] == 'Filter':
        score_func = key_dict["score_func"]
        mode = key_dict["mode"]
    elif key_dict['selector'] == 'Correlation_filter':
        method = key_dict["method"]
    elif key_dict['selector'] == 'Wrapper':
        estimator = key_dict["estimator"]
    elif key_dict['selector'] == "Embedded":
        estimator = key_dict["estimator"]
    elif key_dict['selector'] == "Distance":
        metric = key_dict["metric"]
    else:
        raise Exception("Key is error!")
    return y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, metric


class FEResult():
    def __init__(self, summary_table_name:str = "feature_engineering_summary", result_table_name:str = "feature_engineering_result"):
        self.summary_table_name = summary_table_name
        self.result_table_name = result_table_name
        self.feature_key = self.get_FE_result(return_feature_key = True)

    @staticmethod
    def connect_FE_db():
        DB = mysqldb.MysqlDB(host = "192.168.3.152", user = "root", password = "tj2902",
                       db = "feature_engineering", port = 3306, charaset = 'UTF8')
        return DB

    def FE_to_sql(self, FE_result:list):
        FE_result_key = []
        for item in FE_result:
            result_key = deepcopy(item)
            temp_result = result_key.pop("features")
            FE_result_key.append(str(result_key))
        FE_result = {FE_result_key[i]:FE_result[i] for i in range(len(FE_result))}
        db = self.connect_FE_db()
        update_FE_result_list=[]
        for item in FE_result_key:
            if item in self.feature_key:
                pass
            else:
                update_FE_result_list.append(item)
        update_FE_result = {key: FE_result[key] for key in update_FE_result_list}
        for key, value in update_FE_result.items():
            y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, metric = key_split(key)         
            sql1 = "INSERT INTO " + self.summary_table_name + "(feature_key, y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, metric) "
            sql1 += "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            db.insertdb(sql1, data = (key, y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, metric))
            group_data = []
            for i in range(len(value["features"])):
                group_data.append((key , str(i) , value["features"][i]))
            sql2 = "INSERT INTO " + self.result_table_name + "(feature_key, feature_number, feature_name) VALUES (%s, %s, %s)" 
            if len(value["features"]) > 1:
                db.insertdb(sql2, len(value["features"]), group_data)
            else:
                raise Exception("The length of value is error!")
            
    def get_FE_result(self, customize:bool = False, y_name:tuple = None, freq:tuple = None, end_datetime:tuple = None, test_len:tuple = None,
                 periods:tuple = None, feature_selector:tuple = None, score_func:tuple = None, mode:tuple = None, method:tuple = None,\
                     estimator:tuple = None, metric:tuple = None, num_feature:int = 1000, return_feature_key:bool = False):      
        db = self.connect_FE_db()
        
        if customize:
            sql1 = "SELECT DISTINCT feature_key FROM " + self.summary_table_name + " WHERE "
            if y_name != None: sql1 += "y_name in (%s)"%(',' .join(["'%s'" % item for item in y_name]))
            select_name = ["freq", "end_datetime", "test_len", "periods", "feature_selector", "score_func", "mode", "method", "estimator", "metric"]
            i = 0
            for items in [freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, metric]:
                if items != None:
                    sql1 += " AND %s in (%s)"%(select_name[i], ',' .join(["'%s'" % item for item in items]))
                i += 1
        else:
            sql1 = "SELECT DISTINCT feature_key FROM " + self.summary_table_name
            
        feature_key = db.querydb(sql1, data_format="dataframe")
        feature_key = list(feature_key.iloc[:,0])
        #为真,返回查询的key，否则返回查询的结果
        if return_feature_key:
            return feature_key
        else:
            result = self.query_FE_result(feature_key, num_feature = num_feature)
            return result
    
    def query_FE_result(self, feature_key:list, num_feature:int = 1000):
        db = self.connect_FE_db()
        result = []
        sql2 = "SELECT DISTINCT * FROM " + self.result_table_name +" WHERE feature_key in (%s) AND feature_number in (%s)\
            "%(',' .join(['\"%s\"' % item for item in feature_key]), ',' .join(["'%s'" % num for num in range(num_feature)]))           
        query_result = db.querydb(sql2, data_format = 'dataframe')
        query_result = query_result.iloc[:,1:]
        query_result.drop_duplicates(inplace = True)    
        query_result.reset_index(inplace=True, drop=True)
        for item in feature_key:
            item_dict = eval(item)
            temp_result = query_result[query_result["feature_key"] == item]
            temp_result = temp_result["feature_name"]
            temp_result = temp_result.dropna()  
            temp_result = temp_result.reset_index(drop = True)
            item_dict["features"] = temp_result
            result.append(item_dict)
        return result
    
if __name__ == '__main__':
# =============================================================================
#     selector_path = "..//..//output//selector//selector_2021_02_04_12_42_10_729551"
#     selector_dir = get_file_list(selector_path, [])
#     for item in selector_dir:
#         FE_result = read_pickle(item)
#         FEResult().FE_to_sql(FE_result = FE_result)
# =============================================================================
    #find_FE_result = FEResult().get_FE_result()
    find_FE_result = FEResult().get_FE_result(customize = True, y_name = ("VAD",))
    #find_FE_result = FEResult().get_FE_result(customize = True, y_name = ("工业增加值",), periods = ("24",),feature_selector=('filter',),score_func=('f_regression',),mode=('fwe',) , num_feature = 10)
# =============================================================================
#     save_path = "..//..//output//selector//固定资产投资完成额_基础设施建设投资(不含电力)_累计同比_selector_"+current_time()+".pkl"
#     with open(save_path,"wb") as f:
#          pickle.dump(find_FE_result, f)
# 
# =============================================================================
